﻿Option Explicit On
Option Strict On
Imports System.Data
Imports System.Text
Imports System.Configuration
Imports MySql.Data.MySqlClient

Public Class frmReturn

    Dim Conn As New MySqlConnection()
    Dim com As New MySqlCommand()
    Dim sb As New StringBuilder()
    Dim dr As MySqlDataReader
    Dim dt As DataTable
    Dim tr As MySqlTransaction
    Dim id_b As String
    Dim amount As String

    Private Sub frmReturn_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim strCon As String = ConfigurationManager.ConnectionStrings("Equ").ConnectionString
        If Conn.State = ConnectionState.Open Then
            Conn.Close()
        End If
        Conn.ConnectionString = strCon
        Conn.Open()
        ShowItem()
        FormatItem()
    End Sub

    Private Sub ShowItem()
        sb.Remove(0, sb.Length)
        sb.Append("SELECT borrowdetail.id_borrow,borrowdetail.id_item,item.Name,borrowdetail.qty,borrow.id_ref,member.Name,borrow.Date ")
        sb.Append("FROM borrowdetail,borrow,item,member ")
        sb.Append("WHERE idBor=id_borrow and id_item = idItem and Member_ID = id_ref and item.type = 1 and borrowdetail.status = 1")
        Dim sql As String = sb.ToString()
        With com
            .CommandText = sql
            .CommandType = CommandType.Text
            .Connection = Conn
            dr = .ExecuteReader()
            If dr.HasRows Then
                dt = New DataTable()
                dt.Load(dr)
                dbView.DataSource = dt
            Else
                dbView.DataSource = Nothing
            End If
        End With
    End Sub

    Private Sub FormatItem()
        If dbView.RowCount > 0 Then
            With dbView
                .Columns(0).HeaderText = "รหัสการยืม"
                .Columns(1).HeaderText = "รหัสอุปกรณ์"
                .Columns(2).HeaderText = "ชื่ออุปกรณ์"
                .Columns(3).HeaderText = "จำนวน"
                .Columns(4).HeaderText = "รหัสผู้ยืม"
                .Columns(5).HeaderText = "ชื่อผู้ยืม"
                .Columns(6).HeaderText = "วันที่ยืม"
                .Columns(0).Width = 50
                .Columns(1).Width = 50
                .Columns(3).Width = 50
            End With
        End If
    End Sub

    Private Sub txtSearch_KeyDown(sender As Object, e As KeyEventArgs) Handles txtSearch.KeyDown
        If txtSearch.Text.Trim() = "" Then
            Exit Sub
        End If
        If e.KeyCode = Keys.Enter Then
            sb.Remove(0, sb.Length)
            sb.Append("SELECT borrowdetail.*,borrow.id_ref,borrow.Date FROM borrowdetail,borrow WHERE idBor=id_borrow AND id_ref = '@id'")
            Dim sql As String = sb.ToString()
            With com
                .CommandText = sql
                .CommandType = CommandType.Text
                .Connection = Conn
                dr = .ExecuteReader()
                If dr.HasRows Then
                    dt = New DataTable()
                    dt.Load(dr)
                    dbView.DataSource = dt
                Else
                    dbView.DataSource = Nothing
                End If
                dr.Close()
            End With
            FormatItem()
        End If
    End Sub

    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
        sb.Remove(0, sb.Length)
        sb.Append("SELECT borrowdetail.*,borrow.id_ref,borrow.Date FROM borrowdetail,borrow WHERE idBor=id_borrow AND id_ref = '@id'")
        Dim sql As String = sb.ToString()
        With com
            .CommandText = sql
            .CommandType = CommandType.Text
            .Connection = Conn
            dr = .ExecuteReader()
            If dr.HasRows Then
                dt = New DataTable()
                dt.Load(dr)
                dbView.DataSource = dt
            Else
                dbView.DataSource = Nothing
            End If
            dr.Close()
        End With
        FormatItem()
    End Sub

    Private Sub dbView_CellMouseUp(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dbView.CellMouseUp
        If e.RowIndex = -1 Then
            Exit Sub
        End If
        lbID.Text = dbView.Rows(e.RowIndex).Cells("id_item").Value.ToString()
        lbID_m.Text = dbView.Rows(e.RowIndex).Cells("id_ref").Value.ToString()
        txtAmonut.Text = dbView.Rows(e.RowIndex).Cells("qty").Value.ToString()
        amount = dbView.Rows(e.RowIndex).Cells("qty").Value.ToString()
        id_b = dbView.Rows(e.RowIndex).Cells("id_borrow").Value.ToString()
        txtAmonut.Focus()
        txtAmonut.SelectAll()
        btnSave.Enabled = True
    End Sub

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        If CInt(txtAmonut.Text.Trim()) > CInt(amount) Then
            MessageBox.Show("คุณคืนอุปกรณ์เกินจำนวนที่ยืมไป กรุณาตรวจสอบด้วย !!!!", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtAmonut.Focus()
            txtAmonut.SelectAll()
        End If
  
        If MessageBox.Show("คุณต้องการคืนอุปกรณ์ ตามจำนวนนี้ใช่หรือไม่ ?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            tr = Conn.BeginTransaction()
            Try
                sb.Remove(0, sb.Length)
                If CInt(txtAmonut.Text.Trim()) = CInt(amount) Then
                    sb.Append("UPDATE borrowdetail SET qty = qty - @amount,status = '0' WHERE id_borrow = @id_b AND id_item = @id_i")
                Else
                    sb.Append("UPDATE borrowdetail SET qty = qty - @amount WHERE id_borrow = @id_b AND id_item = @id_i ")
                End If
                Dim sql As String = sb.ToString()
                With com
                    .CommandText = sql
                    .CommandType = CommandType.Text
                    .Connection = Conn
                    .Transaction = tr
                    .Parameters.Clear()
                    .Parameters.Add("@amount", MySqlDbType.Int32).Value = CInt(txtAmonut.Text.Trim())
                    .Parameters.Add("@id_b", MySqlDbType.Int32).Value = CInt(id_b)
                    .Parameters.Add("@id_i", MySqlDbType.Int32).Value = CInt(lbID.Text)
                    .ExecuteNonQuery()
                    tr.Commit()
                    MessageBox.Show("ทำการคืนเรียบร้อย ", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    lbID.Text = ""
                    lbID_m.Text = ""
                    txtAmonut.Clear()
                    btnSave.Enabled = False
                    ShowItem()
                    FormatItem()
                End With
            Catch ex As Exception
                MessageBox.Show("เกิดข้อมูลผิลพลาดเนื่องจาก " & ex.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                tr.Rollback()
            End Try
        End If
    End Sub
End Class